﻿using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using ControleRepresentante.Data;
using ControleRepresentante.Data.Catalogs;
using ControleRepresentante.Logic;
using ControleRepresentante.Logic.Models;
using MySql.Data.MySqlClient;

namespace ControleRepresentante.Migration.Repositories
{
    internal class ControleRepresentanteRepository : IDisposable
    {
        private readonly DataAccess m_DataAccess;
        private readonly IAreaOperacaoRegistry m_AreaOperacaoRegistry;

        public ControleRepresentanteRepository()
        {
            ConnectionStringSettings connectionStringSettings = ConfigurationManager.ConnectionStrings["ControleRepresentante"];
            IDbConnection connection;
            switch (connectionStringSettings.ProviderName)
            {
                case "MySql.Data.MySqlClient":
                    connection = new MySqlConnection(connectionStringSettings.ConnectionString);
                    break;
                case "System.Data.SqlClient":
                    connection = new SqlConnection(connectionStringSettings.ConnectionString);
                    break;
                default:
                    throw new Exception("Invalid ProviderName");
            }
            connection.Open();
            m_DataAccess = new DataAccess<ControleRepresentanteCatalogStepTypes>(connection, new ControleRepresentanteCatalogInternal(), true);
            AreaOperacaoRegistry areaOperacaoRegistry = new AreaOperacaoRegistry();
            AreaOperacaoRegistry.RegisterControleRepresentanteAreas(areaOperacaoRegistry);
            m_AreaOperacaoRegistry = areaOperacaoRegistry;
        }

        public void Dispose()
        {
            m_DataAccess.Dispose();
        }

        public Guid CreateRepresentante(string nome, bool ativo, string nomePedido, string infoPedido, bool useJolitex)
        {
            Guid id = Guid.NewGuid();
            
            m_DataAccess.ExecuteCommand(@"
                INSERT INTO Representante (Id, Nome, Ativo, UseJolitex)
                VALUES (@Id, @Nome, @Ativo, @UseJolitex)",
                new Parameter("Id", ParameterType.Guid, id),
                new Parameter("Nome", ParameterType.String, nome),
                new Parameter("Ativo", ParameterType.Boolean, ativo),
                new Parameter("UseJolitex", ParameterType.Boolean, useJolitex));

            m_DataAccess.ExecuteCommand(@"
                INSERT INTO RepresentanteInfo (Id, NomePedido, InfoPedido)
                VALUES (@Id, @NomePedido, @InfoPedido)",
                new Parameter("Id", ParameterType.Guid, id),
                new Parameter("NomePedido", ParameterType.String, nomePedido),
                new Parameter("InfoPedido", ParameterType.String, infoPedido));
            
            return id;
        }

        public Guid CreateUser(Guid representanteId, string username, string password, bool ativo, bool isAdmin)
        {
            Guid userId = Guid.NewGuid();
            m_DataAccess.ExecuteCommand(@"
                INSERT INTO SystemUser (Id, Ativo, Username, Password, IsAdmin, RepresentanteId)
                VALUES (@Id,  @Ativo, @Username, @Password, @IsAdmin, @RepresentanteId)",
                new Parameter("Id", ParameterType.Guid, userId),
                new Parameter("RepresentanteId", ParameterType.Guid, representanteId),
                new Parameter("Ativo", ParameterType.Boolean, ativo),
                new Parameter("Username", ParameterType.String, username),
                new Parameter("Password", ParameterType.String, password),
                new Parameter("IsAdmin", ParameterType.Boolean, isAdmin));

            foreach (Area area in AreaOperacaoRegistry.GetAreas())
            {
                Operacao operacaoValue = m_AreaOperacaoRegistry.GetOperacoes(area)
                    .Aggregate(Operacao.None, (current, operacao) => current | operacao);
                m_DataAccess.ExecuteCommand(@"
                    INSERT INTO Permissao (Id, UserId, Area, Operacao)
                    VALUES (@Id, @UserId, @Area, @Operacao)",
                    new Parameter("Id", ParameterType.Guid, Guid.NewGuid()),
                    new Parameter("UserId", ParameterType.Guid, userId),
                    new Parameter("Area", ParameterType.Int32, (int)area),
                    new Parameter("Operacao", ParameterType.Int32, (int)operacaoValue));
            }

            return userId;
        }

        public Guid CreateAttribute(Guid representanteId, string name, AttributeType type)
        {
            Guid id = Guid.NewGuid();
            m_DataAccess.ExecuteCommand(@"
                INSERT INTO Attribute (Id, RepresentanteId, Type, Name)
                VALUES (@Id, @RepresentanteId, @Type, @Name)",
                new Parameter("Id", ParameterType.Guid, id),
                new Parameter("RepresentanteId", ParameterType.Guid, representanteId),
                new Parameter("Type", ParameterType.Int32, (int)type),
                new Parameter("Name", ParameterType.String, name));
            return id;
        }

        public Guid CreateEndereco(Guid detailsHolderId, EnderecoType tipo, bool principal, string rua, string bairro, string cep, string cidade, string estado, string observacao = null)
        {
            Guid id = Guid.NewGuid();
            m_DataAccess.ExecuteCommand(@"
                INSERT INTO Endereco (Id, Tipo, Rua, Numero, Complemento, Bairro, CEP, Cidade, Estado, Principal, Observacao, DetailsHolderId)
                VALUES (@Id, @Tipo, @Rua, @Numero, @Complemento, @Bairro, @CEP, @Cidade, @Estado, @Principal, @Observacao, @DetailsHolderId)",
                new Parameter("Id", ParameterType.Guid, id),
                new Parameter("DetailsHolderId", ParameterType.Guid, detailsHolderId),
                new Parameter("Tipo", ParameterType.Int32, (int)tipo),
                new Parameter("Rua", ParameterType.String, rua),
                new Parameter("Numero", ParameterType.String, String.Empty),
                new Parameter("Complemento", ParameterType.String, String.Empty),
                new Parameter("Bairro", ParameterType.String, bairro),
                new Parameter("CEP", ParameterType.String, cep),
                new Parameter("Cidade", ParameterType.String, cidade),
                new Parameter("Estado", ParameterType.String, estado),
                new Parameter("Principal", ParameterType.Boolean, principal),
                new Parameter("Observacao", ParameterType.String, observacao));
            return id;
        }

        public void CreateFabrica(Guid representanteId, Guid id, string nome, string cnpj, string inscricaoEstadual)
        {
            CreateUsuariosHolder(id, representanteId);

            m_DataAccess.ExecuteCommand(@"
                INSERT INTO Fabrica (UsuariosHolderId, Nome, CNPJ, InscricaoEstadual)
                VALUES (@UsuariosHolderId, @Nome, @CNPJ, @InscricaoEstadual)",
                new Parameter("UsuariosHolderId", ParameterType.Guid, id),
                new Parameter("Nome", ParameterType.String, nome),
                new Parameter("CNPJ", ParameterType.String, cnpj),
                new Parameter("InscricaoEstadual", ParameterType.String, inscricaoEstadual));
        }

        private void CreateUsuariosHolder(Guid id, Guid representanteId)
        {
            CreateAttributeHolder(id, representanteId);

            m_DataAccess.ExecuteCommand(@"
                INSERT INTO DetailsHolder (AttributeHolderId)
                VALUES (@AttributeHolderId)",
                                        new Parameter("AttributeHolderId", ParameterType.Guid, id));
            m_DataAccess.ExecuteCommand(@"
                INSERT INTO UsuariosHolder (DetailsHolderId)
                VALUES (@DetailsHolderId)",
                new Parameter("DetailsHolderId", ParameterType.Guid, id));
        }

        private void CreateAttributeHolder(Guid id, Guid representanteId)
        {
            m_DataAccess.ExecuteCommand(@"
                INSERT INTO AttributeHolder (Id, RepresentanteId)
                VALUES (@Id, @RepresentanteId)",
                new Parameter("Id", ParameterType.Guid, id),
                new Parameter("RepresentanteId", ParameterType.Guid, representanteId));
        }

        public void CreateAttributeValue(Guid attributeHolderId, Guid attributeId, string valor)
        {
            m_DataAccess.ExecuteCommand(@"
                INSERT INTO AttributeValue (Id, AttributeId, Value, AttributeHolderId)
                VALUES (@Id, @AttributeId, @Value, @AttributeHolderId)",
                new Parameter("Id", ParameterType.Guid, Guid.NewGuid()),
                new Parameter("AttributeId", ParameterType.Guid, attributeId),
                new Parameter("Value", ParameterType.String, valor),
                new Parameter("AttributeHolderId", ParameterType.Guid, attributeHolderId));
        }

        public void CreateContato(Guid detailsHolder, ContatoType tipo, bool principal, string valor, string observacao = null)
        {
            if (valor != null)
            {
                m_DataAccess.ExecuteCommand(@"
                    INSERT INTO Contato (Id, Tipo, Valor, Principal, Observacao, DetailsHolderId)
                    VALUES (@Id, @Tipo, @Valor, @Principal, @Observacao, @DetailsHolderId)",
                    new Parameter("Id", ParameterType.Guid, Guid.NewGuid()),
                    new Parameter("Tipo", ParameterType.Int32, (int)tipo),
                    new Parameter("Valor", ParameterType.String, valor),
                    new Parameter("Principal", ParameterType.Boolean, principal),
                    new Parameter("Observacao", ParameterType.String, observacao),
                    new Parameter("DetailsHolderId", ParameterType.Guid, detailsHolder));
            }
        }

        public void CreateConta(Guid detailsHolderId, bool principal, string banco, string agencia, string conta)
        {
            m_DataAccess.ExecuteCommand(@"
                INSERT INTO Conta (Id, Banco, Agencia, Numero, Principal, Observacao, DetailsHolderId)
                VALUES (@Id, @Banco, @Agencia, @Numero, @Principal, @Observacao, @DetailsHolderId)",
                new Parameter("Id", ParameterType.Guid, Guid.NewGuid()),
                new Parameter("DetailsHolderId", ParameterType.Guid, detailsHolderId),
                new Parameter("Banco", ParameterType.String, banco),
                new Parameter("Agencia", ParameterType.String, agencia),
                new Parameter("Numero", ParameterType.String, conta),
                new Parameter("Principal", ParameterType.Boolean, principal),
                new Parameter("Observacao", ParameterType.String, null));
        }

        public void CreateTransportadora(Guid representanteId, Guid id, string nome)
        {
            CreateUsuariosHolder(id, representanteId);

            m_DataAccess.ExecuteCommand(@"
                INSERT INTO Transportadora(UsuariosHolderId, Nome)
                VALUES (@UsuariosHolderId, @Nome)",
                new Parameter("UsuariosHolderId", ParameterType.Guid, id),
                new Parameter("Nome", ParameterType.String, nome));
        }

        public void CreateVendedor(Guid representanteId, Guid id, string cpf, string nome)
        {
            CreateUsuariosHolder(id, representanteId);

            m_DataAccess.ExecuteCommand(@"
                INSERT INTO Vendedor (UsuariosHolderId, Nome, Nascimento, Identidade, CPF)
                VALUES (@UsuariosHolderId, @Nome, @Nascimento, @Identidade, @CPF)",
                new Parameter("UsuariosHolderId", ParameterType.Guid, id),
                new Parameter("Nome", ParameterType.String, nome),
                new Parameter("Nascimento", ParameterType.DateTime, null),
                new Parameter("Identidade", ParameterType.String, cpf),
                new Parameter("CPF", ParameterType.String, cpf));
        }

        public void CreatePedido(Guid representanteId, Guid id, Guid clienteId, Guid fabricaId, Guid transportadoraId, Guid vendedorId, string condicaoPagamento, DateTime data, string observacao, string prazoEntrega, int numero, bool sincronizado, double desconto, bool faturado, bool cancelado, int? codigoJolitex)
        {
            CreateAttributeHolder(id, representanteId);

            m_DataAccess.ExecuteCommand(@"
                INSERT INTO Pedido (AttributeHolderId, ClienteId, CondicaoPagamento, Data, FabricaId, Observacao, PrazoEntrega, TransportadoraId, VendedorId, Numero, Desconto, Faturado, Cancelado, TotalMercadoria, TotalPecas, ValorImposto, TotalLiquido)
                VALUES (@AttributeHolderId, @ClienteId, @CondicaoPagamento, @Data, @FabricaId, @Observacao, @PrazoEntrega, @TransportadoraId, @VendedorId, @Numero, @Desconto, @Faturado, @Cancelado, 0, 0, 0, 0)",
                new Parameter("AttributeHolderId", ParameterType.Guid, id),
                new Parameter("ClienteId", ParameterType.Guid, clienteId),
                new Parameter("CondicaoPagamento", ParameterType.String, condicaoPagamento),
                new Parameter("Data", ParameterType.DateTime, data),
                new Parameter("FabricaId", ParameterType.Guid, fabricaId),
                new Parameter("Observacao", ParameterType.String, observacao),
                new Parameter("PrazoEntrega", ParameterType.String, prazoEntrega),
                new Parameter("TransportadoraId", ParameterType.Guid, transportadoraId),
                new Parameter("Numero", ParameterType.Int32, numero),
                new Parameter("VendedorId", ParameterType.Guid, vendedorId),
                new Parameter("Desconto", ParameterType.Float, desconto),
                new Parameter("Faturado", ParameterType.Boolean, faturado),
                new Parameter("Cancelado", ParameterType.Boolean, cancelado));

            m_DataAccess.ExecuteCommand(@"
                INSERT INTO PedidoJolitex (Id, Sincronizado, Codigo)
                VALUES (@Id, @Sincronizado, @Codigo)",
                new Parameter("Id", ParameterType.Guid, id),
                new Parameter("Codigo", ParameterType.Int32, codigoJolitex),
                new Parameter("Sincronizado", ParameterType.Boolean, sincronizado));
        }

        public void CreateProduto(Guid pedidoId, string codigo, string descricao, double precoUnitario, int quantidade)
        {
            m_DataAccess.ExecuteCommand(@"
                INSERT INTO Produto (Id, PedidoId, Codigo, Descricao, PrecoUnitario, Quantidade)
                VALUES (@Id, @PedidoId, @Codigo, @Descricao, @PrecoUnitario, @Quantidade)",
                new Parameter("Id", ParameterType.Guid, Guid.NewGuid()),
                new Parameter("PedidoId", ParameterType.Guid, pedidoId),
                new Parameter("Codigo", ParameterType.String, codigo),
                new Parameter("Descricao", ParameterType.String, descricao),
                new Parameter("PrecoUnitario", ParameterType.Float, precoUnitario),
                new Parameter("Quantidade", ParameterType.Int32, quantidade));
        }

        public void CreateCliente(Guid representanteId, Guid id, Guid vendedorId, string cnpj, string inscricaoEstadual, string razaoSocial)
        {
            CreateUsuariosHolder(id, representanteId);

            m_DataAccess.ExecuteCommand(@"
                INSERT INTO Cliente (UsuariosHolderId, VendedorId, CNPJ, InscricaoEstadual, RazaoSocial, NomeFantasia)
                VALUES (@UsuariosHolderId, @VendedorId, @CNPJ, @InscricaoEstadual, @RazaoSocial, @NomeFantasia)",
                new Parameter("UsuariosHolderId", ParameterType.Guid, id),
                new Parameter("VendedorId", ParameterType.Guid, vendedorId),
                new Parameter("CNPJ", ParameterType.String, cnpj),
                new Parameter("InscricaoEstadual", ParameterType.String, inscricaoEstadual),
                new Parameter("RazaoSocial", ParameterType.String, razaoSocial),
                new Parameter("NomeFantasia", ParameterType.String, razaoSocial));
        }

        public void SetEnderecosForCliente(Guid clienteId, Guid enderecoEntregaId, Guid enderecoCobrancaId)
        {
            m_DataAccess.ExecuteCommand(@"
                UPDATE Cliente
                SET
                    EnderecoEntregaId = @EnderecoEntregaId,
                    EnderecoCobrancaId = @EnderecoCobrancaId
                WHERE UsuariosHolderId = @UsuariosHolderId",
                new Parameter("EnderecoEntregaId", ParameterType.Guid, enderecoEntregaId),
                new Parameter("EnderecoCobrancaId", ParameterType.Guid, enderecoCobrancaId),
                new Parameter("UsuariosHolderId", ParameterType.Guid, clienteId));
        }

        public void CreateClienteJolitex(Guid clienteId, int? codigo, int? regiao)
        {
            m_DataAccess.ExecuteCommand(@"
                INSERT INTO ClienteJolitex(Id, Codigo, Regiao)
                VALUES (@Id, @Codigo, @Regiao)",
                new Parameter("Id", ParameterType.Guid, clienteId),
                new Parameter("Codigo", ParameterType.Int32, codigo),
                new Parameter("Regiao", ParameterType.Int32, regiao));
        }

        public void CreateFabricaJolitex(Guid fabricaId, bool isJolitex)
        {
            m_DataAccess.ExecuteCommand(@"
                INSERT INTO FabricaJolitex(Id, IsJolitex)
                VALUES (@Id, @IsJolitex)",
                new Parameter("Id", ParameterType.Guid, fabricaId),
                new Parameter("IsJolitex", ParameterType.Boolean, isJolitex));
        }

        public void CreatePost(Guid pedidoId, Guid userId, DateTime data, string comentario)
        {
            m_DataAccess.ExecuteCommand(@"
                INSERT INTO Post (Id, UserId, PedidoId, Data, Comentario)
                VALUES (@Id, @UserId, @PedidoId, @Data, @Comentario)",
                new Parameter("Id", ParameterType.Guid, Guid.NewGuid()),
                new Parameter("UserId", ParameterType.Guid, userId),
                new Parameter("PedidoId", ParameterType.Guid, pedidoId),
                new Parameter("Data", ParameterType.DateTime, data),
                new Parameter("Comentario", ParameterType.String, comentario));
        }

        public void CalculateTotalMercadoria()
        {
            m_DataAccess.ExecuteCommand(@"
                UPDATE Pedido
                SET TotalMercadoria = CASE WHEN (SELECT COUNT(*) FROM Produto WHERE PedidoId = AttributeHolderId) = 0 THEN 0 ELSE (SELECT SUM(Quantidade * PrecoUnitario) FROM Produto WHERE PedidoId = AttributeHolderId) END");
        }

        public void CalculateTotalPecas()
        {
            m_DataAccess.ExecuteCommand(@"
                UPDATE Pedido
                SET TotalPecas = CASE WHEN (SELECT COUNT(*) FROM Produto WHERE PedidoId = AttributeHolderId) = 0 THEN 0 ELSE (SELECT SUM(Quantidade) FROM Produto WHERE PedidoId = AttributeHolderId) END");
        }

        public void CalculateValorImposto()
        {
            m_DataAccess.ExecuteCommand(@"
                UPDATE Pedido
                SET ValorImposto = CASE WHEN (SELECT COUNT(*) FROM Produto WHERE PedidoId = AttributeHolderId) = 0 THEN 0 ELSE (SELECT SUM(PrecoUnitario * Quantidade * (CASE WHEN Descricao LIKE '%tap%' OR Descricao LIKE '%banh%' THEN 0.1 ELSE 0 END)) FROM Produto WHERE PedidoId = AttributeHolderId) END");
        }

        public void CalculateTotalLiquido()
        {
            m_DataAccess.ExecuteCommand(@"
                UPDATE Pedido
                SET TotalLiquido = TotalMercadoria - (TotalMercadoria * Desconto / 100) + ValorImposto");
        }

        public Guid GetUserId()
        {
            return (Guid) m_DataAccess.ExecuteScalar("SELECT Id FROM SystemUser WHERE Username = 'u'");
        }

        public Guid GetRepresentanteId()
        {
            return (Guid) m_DataAccess.ExecuteScalar("SELECT Id FROM Representante");
        }
    }
}
